package com.wanxi.dao.Impl;

import com.wanxi.dao.NewsDao;
import com.wanxi.model.News;
import com.wanxi.model.NewsType;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl implements NewsDao {
    @Override
    public List<News> findAllNews() {
        List<News> list=new ArrayList<>();
        String sql="select n.*,nt.newsTypeName from news n,news_type nt where n.newsTypeID=nt.id";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()){
                list.add(News.builder()
                        .id(query.getInt("id"))
                        .title(query.getString("title"))
                        .date(query.getDate("date"))
                        .content(query.getString("content"))
                        .enable(query.getInt("enable"))
                        .createTime(query.getTimestamp("createTime"))
                        .updateTime(query.getTimestamp("updateTime"))
                        .remark(query.getString("remark"))
                        .newsType(NewsType.builder()
                                .id(query.getInt("newsTypeID"))
                                .newsTypeName(query.getString("newsTypeName"))
                                .build())
                        .build());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public News findNewsById(News news) {
        String sql="select n.*,nt.newsTypeName from news n,news_type nt where n.id="+news.getId();
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()){
                return News.builder()
                        .id(query.getInt("id"))
                        .title(query.getString("title"))
                        .date(query.getDate("date"))
                        .content(query.getString("content"))
                        .enable(query.getInt("enable"))
                        .createTime(query.getTimestamp("createTime"))
                        .updateTime(query.getTimestamp("updateTime"))
                        .remark(query.getString("remark"))
                        .newsType(NewsType.builder()
                                .id(query.getInt("newsTypeID"))
                                .newsTypeName(query.getString("newsTypeName"))
                                .build())
                        .build();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    @Override
    public int updateEnable(News news) {
        String sql="update news set enable="+news.getEnable()+",updateTime=(select now()) where id="+news.getId();
        return JDBC.update(sql);
    }

    @Override
    public int addNews(News news) {
        String sql="insert into news(title,content,date,enable,remark,newsTypeID,createTime,updateTime) " +
                "values('"+news.getTitle()
                +"','"+news.getContent()
                +"','"+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(news.getDate())
                +"',"+news.getEnable()
                +",'"+news.getRemark()
                +"',"+news.getNewsType().getId()
                +",(select now()),(select now()))";
        return JDBC.update(sql);
    }

    @Override
    public int deleteNews(News news) {
        String sql="delete from news where id="+news.getId();
        return JDBC.update(sql);
    }

    @Override
    public int updateNews(News news) {
        String sql="update news set title='"+news.getTitle()
                +"',content='"+news.getContent()
                +"',date='"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(news.getDate())
                +"',enable="+news.getEnable()
                +",remark='"+news.getRemark()
                +"',newsTypeID="+news.getNewsType().getId()
                +",updateTime=(select now()) where id="+news.getId();
        return JDBC.update(sql);
    }

    @Override
    public List<News> findNews(News news) {
        List<News> list=new ArrayList<>();
        String sql="select n.*,nt.newsTypeName from news n,news_type nt where n.newsTypeID=nt.id and n.newsTypeID="+news.getNewsType().getId();
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()){
                list.add(News.builder()
                        .id(query.getInt("id"))
                        .title(query.getString("title"))
                        .date(query.getDate("date"))
                        .content(query.getString("content"))
                        .enable(query.getInt("enable"))
                        .createTime(query.getTimestamp("createTime"))
                        .updateTime(query.getTimestamp("updateTime"))
                        .remark(query.getString("remark"))
                        .newsType(NewsType.builder()
                                .id(query.getInt("newsTypeID"))
                                .newsTypeName(query.getString("newsTypeName"))
                                .build())
                        .build());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public List<News> findNewsBySome(News news) {
        List<News> list=new ArrayList<>();
        String sql="select n.*,nt.newsTypeName from news n,news_type nt where n.newsTypeID=nt.id";
        if(news.getTitle()!=null){
            sql+=" and n.title like '%"+news.getTitle()+"%'";
        }
        if (news.getContent()!=null){
            sql+=" and n.content like '%"+news.getContent()+"%'";
        }
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()){
                list.add(News.builder()
                        .id(query.getInt("id"))
                        .title(query.getString("title"))
                        .date(query.getDate("date"))
                        .content(query.getString("content"))
                        .enable(query.getInt("enable"))
                        .createTime(query.getTimestamp("createTime"))
                        .updateTime(query.getTimestamp("updateTime"))
                        .remark(query.getString("remark"))
                        .newsType(NewsType.builder()
                                .id(query.getInt("newsTypeID"))
                                .newsTypeName(query.getString("newsTypeName"))
                                .build())
                        .build());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
}
